{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 规模和价值投资组合"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 规模"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Size factor：小公司的未来表现总是超过大公司\n",
    "\n",
    "Value factor：高价值的公司（Value Stock）（高BM EP的公司）表现超过了成长股（Growth Stock）\n",
    "\n",
    "Beta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "异象 Anomaly\n",
    "\n",
    "Fama and French(1993)将规模组合当作了一个定价因子，从而成为投资策略的评判基准之一。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np # 数据处理最重要的模块\n",
    "import pandas as pd # 数据处理最重要的模块\n",
    "import scipy.stats as stats # 统计模块\n",
    "import scipy\n",
    "# import pymysql  # 导入数据库模块\n",
    "\n",
    "from datetime import datetime # 时间模块\n",
    "import statsmodels.formula.api as smf  # OLS regression\n",
    "\n",
    "# import pyreadr # read RDS file\n",
    "\n",
    "from matplotlib import style\n",
    "import matplotlib.pyplot as plt  # 画图模块\n",
    "import matplotlib.dates as mdates\n",
    "\n",
    "from matplotlib.font_manager import FontProperties # 作图中文\n",
    "from pylab import mpl\n",
    "#mpl.rcParams['font.sans-serif'] = ['SimHei']\n",
    "#plt.rcParams['font.family'] = 'Times New Roman'\n",
    "\n",
    "#输出矢量图 渲染矢量图\n",
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format = 'svg'\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell # jupyter运行输出的模块\n",
    "#显示每一个运行结果\n",
    "InteractiveShell.ast_node_interactivity = 'all'\n",
    "\n",
    "#设置行不限制数量\n",
    "#pd.set_option('display.max_rows',None)\n",
    "\n",
    "#设置列不限制数量\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>38.34</td>\n",
       "      <td>2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.016010e+09</td>\n",
       "      <td>1.859497e+09</td>\n",
       "      <td>20.739149</td>\n",
       "      <td>21.343572</td>\n",
       "      <td>-0.122253</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.128345</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-06-30</td>\n",
       "      <td>33.99</td>\n",
       "      <td>3</td>\n",
       "      <td>23.0</td>\n",
       "      <td>9.007350e+08</td>\n",
       "      <td>1.648521e+09</td>\n",
       "      <td>20.618722</td>\n",
       "      <td>21.223144</td>\n",
       "      <td>-0.113459</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-07-31</td>\n",
       "      <td>29.54</td>\n",
       "      <td>4</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7.828100e+08</td>\n",
       "      <td>1.432695e+09</td>\n",
       "      <td>20.478401</td>\n",
       "      <td>21.082823</td>\n",
       "      <td>-0.130921</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-08-31</td>\n",
       "      <td>15.00</td>\n",
       "      <td>5</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.748338e+08</td>\n",
       "      <td>1.346275e+09</td>\n",
       "      <td>20.329977</td>\n",
       "      <td>21.020607</td>\n",
       "      <td>-0.411588</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-09-30</td>\n",
       "      <td>14.50</td>\n",
       "      <td>6</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6.523394e+08</td>\n",
       "      <td>1.301399e+09</td>\n",
       "      <td>20.296075</td>\n",
       "      <td>20.986706</td>\n",
       "      <td>-0.033333</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>0.849080</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752023</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752024</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752025</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752026</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752027</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>14.89</td>\n",
       "      <td>28</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.735632e+09</td>\n",
       "      <td>1.158111e+10</td>\n",
       "      <td>22.890676</td>\n",
       "      <td>23.172641</td>\n",
       "      <td>0.104599</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "1       000001 1991-05-31  38.34     2  24.0   1.016010e+09  1.859497e+09   \n",
       "2       000001 1991-06-30  33.99     3  23.0   9.007350e+08  1.648521e+09   \n",
       "3       000001 1991-07-31  29.54     4  16.0   7.828100e+08  1.432695e+09   \n",
       "4       000001 1991-08-31  15.00     5  15.0   6.748338e+08  1.346275e+09   \n",
       "5       000001 1991-09-30  14.50     6  24.0   6.523394e+08  1.301399e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "752023  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "752024  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "752025  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "752026  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "752027  605599 2023-12-31  14.89    28  21.0   8.735632e+09  1.158111e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \n",
       "1       20.739149  21.343572 -0.122253  0.006092 -0.128345 -0.119551  1  \n",
       "2       20.618722  21.223144 -0.113459  0.006092 -0.119551 -0.137013  1  \n",
       "3       20.478401  21.082823 -0.130921  0.006092 -0.137013 -0.417680  1  \n",
       "4       20.329977  21.020607 -0.411588  0.006092 -0.417680 -0.039425  1  \n",
       "5       20.296075  20.986706 -0.033333  0.006092 -0.039425  0.849080  1  \n",
       "...           ...        ...       ...       ...       ...       ... ..  \n",
       "752023  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1  \n",
       "752024  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1  \n",
       "752025  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1  \n",
       "752026  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1  \n",
       "752027  22.890676  23.172641  0.104599  0.001241  0.103358       NaN  1  \n",
       "\n",
       "[709883 rows x 14 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cross = pd.read_csv('C:/Users/21230/Desktop/Python-homework/python-homework-ykl-1/ret_mon_python2023.csv')\n",
    "from pandas.tseries.offsets import MonthEnd\n",
    "cross['month'] = pd.to_datetime(cross['month'], format='%Y-%m-%d') + MonthEnd(1)\n",
    "# 补齐股票代码 如果不满6位 在前面补上0\n",
    "cross['Stkcd'] = cross['Stkcd'].apply(lambda x: '{:0>6}'.format(x)) # 6位股票代码\n",
    "cross['w'] = 1\n",
    "cross = cross.dropna(subset=['ret','totalvalue'])\n",
    "cross"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1991-01-31</th>\n",
       "      <td>7.499850e+06</td>\n",
       "      <td>1.324290e+07</td>\n",
       "      <td>4.841034e+07</td>\n",
       "      <td>8.357778e+07</td>\n",
       "      <td>1.645389e+08</td>\n",
       "      <td>2.455000e+08</td>\n",
       "      <td>5.287392e+08</td>\n",
       "      <td>8.119784e+08</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-02-28</th>\n",
       "      <td>1.168762e+07</td>\n",
       "      <td>1.787684e+07</td>\n",
       "      <td>6.941160e+07</td>\n",
       "      <td>1.426559e+08</td>\n",
       "      <td>2.168625e+08</td>\n",
       "      <td>4.029251e+08</td>\n",
       "      <td>6.804663e+08</td>\n",
       "      <td>8.582599e+08</td>\n",
       "      <td>1.065150e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-03-31</th>\n",
       "      <td>1.098601e+07</td>\n",
       "      <td>1.691778e+07</td>\n",
       "      <td>4.002500e+07</td>\n",
       "      <td>7.086472e+07</td>\n",
       "      <td>1.199623e+08</td>\n",
       "      <td>3.103026e+08</td>\n",
       "      <td>6.328671e+08</td>\n",
       "      <td>8.540667e+08</td>\n",
       "      <td>9.379800e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-04-30</th>\n",
       "      <td>1.106975e+07</td>\n",
       "      <td>1.805230e+07</td>\n",
       "      <td>3.729500e+07</td>\n",
       "      <td>6.259785e+07</td>\n",
       "      <td>1.070732e+08</td>\n",
       "      <td>2.760596e+08</td>\n",
       "      <td>5.889068e+08</td>\n",
       "      <td>8.629674e+08</td>\n",
       "      <td>9.611433e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991-05-31</th>\n",
       "      <td>1.310760e+07</td>\n",
       "      <td>2.075000e+07</td>\n",
       "      <td>3.930000e+07</td>\n",
       "      <td>7.435439e+07</td>\n",
       "      <td>1.237500e+08</td>\n",
       "      <td>4.656752e+08</td>\n",
       "      <td>8.200000e+08</td>\n",
       "      <td>9.495000e+08</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>2.409184e+09</td>\n",
       "      <td>2.989534e+09</td>\n",
       "      <td>3.635605e+09</td>\n",
       "      <td>4.424176e+09</td>\n",
       "      <td>5.520202e+09</td>\n",
       "      <td>7.084067e+09</td>\n",
       "      <td>9.755098e+09</td>\n",
       "      <td>1.501682e+10</td>\n",
       "      <td>2.876172e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-09-30</th>\n",
       "      <td>2.421632e+09</td>\n",
       "      <td>2.985093e+09</td>\n",
       "      <td>3.638369e+09</td>\n",
       "      <td>4.403628e+09</td>\n",
       "      <td>5.487673e+09</td>\n",
       "      <td>7.058126e+09</td>\n",
       "      <td>9.799861e+09</td>\n",
       "      <td>1.484667e+10</td>\n",
       "      <td>2.831428e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-10-31</th>\n",
       "      <td>2.409577e+09</td>\n",
       "      <td>2.957719e+09</td>\n",
       "      <td>3.627091e+09</td>\n",
       "      <td>4.384522e+09</td>\n",
       "      <td>5.401911e+09</td>\n",
       "      <td>6.984732e+09</td>\n",
       "      <td>9.608270e+09</td>\n",
       "      <td>1.451708e+10</td>\n",
       "      <td>2.725788e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-11-30</th>\n",
       "      <td>2.539108e+09</td>\n",
       "      <td>3.126053e+09</td>\n",
       "      <td>3.791893e+09</td>\n",
       "      <td>4.588888e+09</td>\n",
       "      <td>5.702116e+09</td>\n",
       "      <td>7.194539e+09</td>\n",
       "      <td>9.833215e+09</td>\n",
       "      <td>1.482454e+10</td>\n",
       "      <td>2.753897e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>2.478696e+09</td>\n",
       "      <td>3.076500e+09</td>\n",
       "      <td>3.703741e+09</td>\n",
       "      <td>4.496844e+09</td>\n",
       "      <td>5.579697e+09</td>\n",
       "      <td>6.945948e+09</td>\n",
       "      <td>9.630063e+09</td>\n",
       "      <td>1.432934e+10</td>\n",
       "      <td>2.676280e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>396 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     one           two         three          four  \\\n",
       "month                                                                \n",
       "1991-01-31  7.499850e+06  1.324290e+07  4.841034e+07  8.357778e+07   \n",
       "1991-02-28  1.168762e+07  1.787684e+07  6.941160e+07  1.426559e+08   \n",
       "1991-03-31  1.098601e+07  1.691778e+07  4.002500e+07  7.086472e+07   \n",
       "1991-04-30  1.106975e+07  1.805230e+07  3.729500e+07  6.259785e+07   \n",
       "1991-05-31  1.310760e+07  2.075000e+07  3.930000e+07  7.435439e+07   \n",
       "...                  ...           ...           ...           ...   \n",
       "2023-08-31  2.409184e+09  2.989534e+09  3.635605e+09  4.424176e+09   \n",
       "2023-09-30  2.421632e+09  2.985093e+09  3.638369e+09  4.403628e+09   \n",
       "2023-10-31  2.409577e+09  2.957719e+09  3.627091e+09  4.384522e+09   \n",
       "2023-11-30  2.539108e+09  3.126053e+09  3.791893e+09  4.588888e+09   \n",
       "2023-12-31  2.478696e+09  3.076500e+09  3.703741e+09  4.496844e+09   \n",
       "\n",
       "                    five           six         seven         eight  \\\n",
       "month                                                                \n",
       "1991-01-31  1.645389e+08  2.455000e+08  5.287392e+08  8.119784e+08   \n",
       "1991-02-28  2.168625e+08  4.029251e+08  6.804663e+08  8.582599e+08   \n",
       "1991-03-31  1.199623e+08  3.103026e+08  6.328671e+08  8.540667e+08   \n",
       "1991-04-30  1.070732e+08  2.760596e+08  5.889068e+08  8.629674e+08   \n",
       "1991-05-31  1.237500e+08  4.656752e+08  8.200000e+08  9.495000e+08   \n",
       "...                  ...           ...           ...           ...   \n",
       "2023-08-31  5.520202e+09  7.084067e+09  9.755098e+09  1.501682e+10   \n",
       "2023-09-30  5.487673e+09  7.058126e+09  9.799861e+09  1.484667e+10   \n",
       "2023-10-31  5.401911e+09  6.984732e+09  9.608270e+09  1.451708e+10   \n",
       "2023-11-30  5.702116e+09  7.194539e+09  9.833215e+09  1.482454e+10   \n",
       "2023-12-31  5.579697e+09  6.945948e+09  9.630063e+09  1.432934e+10   \n",
       "\n",
       "                    nine  \n",
       "month                     \n",
       "1991-01-31  9.109892e+08  \n",
       "1991-02-28  1.065150e+09  \n",
       "1991-03-31  9.379800e+08  \n",
       "1991-04-30  9.611433e+08  \n",
       "1991-05-31  1.179490e+09  \n",
       "...                  ...  \n",
       "2023-08-31  2.876172e+10  \n",
       "2023-09-30  2.831428e+10  \n",
       "2023-10-31  2.725788e+10  \n",
       "2023-11-30  2.753897e+10  \n",
       "2023-12-31  2.676280e+10  \n",
       "\n",
       "[396 rows x 9 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fenweishu = pd.DataFrame(\n",
    "    cross.groupby(['month'])['totalvalue'].quantile([0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]))\n",
    "fenweishu = fenweishu.reset_index()\n",
    "fenweishu = fenweishu.pivot_table(index='month',columns='level_1',values='totalvalue')\n",
    "fenweishu.columns = ['one','two','three','four','five','six','seven','eight','nine']\n",
    "fenweishu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>38.34</td>\n",
       "      <td>2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.016010e+09</td>\n",
       "      <td>1.859497e+09</td>\n",
       "      <td>20.739149</td>\n",
       "      <td>21.343572</td>\n",
       "      <td>-0.122253</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.128345</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>11.29</td>\n",
       "      <td>5</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.161200e+08</td>\n",
       "      <td>4.656752e+08</td>\n",
       "      <td>19.571632</td>\n",
       "      <td>19.958999</td>\n",
       "      <td>-0.030901</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.036993</td>\n",
       "      <td>-0.309901</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>9.90</td>\n",
       "      <td>5</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4.950000e+07</td>\n",
       "      <td>1.237500e+08</td>\n",
       "      <td>17.717483</td>\n",
       "      <td>18.633774</td>\n",
       "      <td>-0.114490</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.120582</td>\n",
       "      <td>-0.460637</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000005</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>10.55</td>\n",
       "      <td>5</td>\n",
       "      <td>20.0</td>\n",
       "      <td>4.502265e+08</td>\n",
       "      <td>9.495000e+08</td>\n",
       "      <td>19.925261</td>\n",
       "      <td>20.671446</td>\n",
       "      <td>-0.123026</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.129118</td>\n",
       "      <td>-0.115097</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600601</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>39.30</td>\n",
       "      <td>6</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3.576300e+07</td>\n",
       "      <td>3.930000e+07</td>\n",
       "      <td>17.392424</td>\n",
       "      <td>17.486735</td>\n",
       "      <td>-0.124722</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.130814</td>\n",
       "      <td>0.184748</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>600602</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>505.00</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.479550e+08</td>\n",
       "      <td>1.010000e+09</td>\n",
       "      <td>19.328758</td>\n",
       "      <td>20.733216</td>\n",
       "      <td>0.009798</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.002868</td>\n",
       "      <td>0.010496</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>600651</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>401.30</td>\n",
       "      <td>2</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.324290e+07</td>\n",
       "      <td>1.324290e+07</td>\n",
       "      <td>16.398972</td>\n",
       "      <td>16.398972</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>-0.041567</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>600652</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>219.60</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.756800e+06</td>\n",
       "      <td>1.756800e+06</td>\n",
       "      <td>14.379005</td>\n",
       "      <td>14.379005</td>\n",
       "      <td>0.062409</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.055479</td>\n",
       "      <td>0.028134</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>600654</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>397.80</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8.353800e+06</td>\n",
       "      <td>8.357778e+07</td>\n",
       "      <td>15.938227</td>\n",
       "      <td>18.241288</td>\n",
       "      <td>0.041089</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.034159</td>\n",
       "      <td>0.081305</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>600656</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>329.20</td>\n",
       "      <td>2</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.481762e+08</td>\n",
       "      <td>8.119784e+08</td>\n",
       "      <td>18.813913</td>\n",
       "      <td>20.514984</td>\n",
       "      <td>0.088624</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.081694</td>\n",
       "      <td>-0.002070</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month   price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "0       000001 1991-05-31   38.34     2  24.0   1.016010e+09  1.859497e+09   \n",
       "1       000002 1991-05-31   11.29     5  17.0   3.161200e+08  4.656752e+08   \n",
       "2       000004 1991-05-31    9.90     5  16.0   4.950000e+07  1.237500e+08   \n",
       "3       000005 1991-05-31   10.55     5  20.0   4.502265e+08  9.495000e+08   \n",
       "4       600601 1991-05-31   39.30     6  22.0   3.576300e+07  3.930000e+07   \n",
       "...        ...        ...     ...   ...   ...            ...           ...   \n",
       "709878  600602 1991-01-31  505.00     2  22.0   2.479550e+08  1.010000e+09   \n",
       "709879  600651 1991-01-31  401.30     2  20.0   1.324290e+07  1.324290e+07   \n",
       "709880  600652 1991-01-31  219.60     2  11.0   1.756800e+06  1.756800e+06   \n",
       "709881  600654 1991-01-31  397.80     2  22.0   8.353800e+06  8.357778e+07   \n",
       "709882  600656 1991-01-31  329.20     2  14.0   1.481762e+08  8.119784e+08   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "0       20.739149  21.343572 -0.122253  0.006092 -0.128345 -0.119551  1   \n",
       "1       19.571632  19.958999 -0.030901  0.006092 -0.036993 -0.309901  1   \n",
       "2       17.717483  18.633774 -0.114490  0.006092 -0.120582 -0.460637  1   \n",
       "3       19.925261  20.671446 -0.123026  0.006092 -0.129118 -0.115097  1   \n",
       "4       17.392424  17.486735 -0.124722  0.006092 -0.130814  0.184748  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "709878  19.328758  20.733216  0.009798  0.006930  0.002868  0.010496  1   \n",
       "709879  16.398972  16.398972  0.000000  0.006930 -0.006930 -0.041567  1   \n",
       "709880  14.379005  14.379005  0.062409  0.006930  0.055479  0.028134  1   \n",
       "709881  15.938227  18.241288  0.041089  0.006930  0.034159  0.081305  1   \n",
       "709882  18.813913  20.514984  0.088624  0.006930  0.081694 -0.002070  1   \n",
       "\n",
       "               one         two       three        four         five  \\\n",
       "0       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "1       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "2       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "3       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "4       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "...            ...         ...         ...         ...          ...   \n",
       "709878   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709879   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709880   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709881   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709882   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "\n",
       "                six        seven        eight          nine  \n",
       "0       465675240.0  820000000.0  949500000.0  1.179490e+09  \n",
       "1       465675240.0  820000000.0  949500000.0  1.179490e+09  \n",
       "2       465675240.0  820000000.0  949500000.0  1.179490e+09  \n",
       "3       465675240.0  820000000.0  949500000.0  1.179490e+09  \n",
       "4       465675240.0  820000000.0  949500000.0  1.179490e+09  \n",
       "...             ...          ...          ...           ...  \n",
       "709878  245500000.0  528739190.0  811978380.0  9.109892e+08  \n",
       "709879  245500000.0  528739190.0  811978380.0  9.109892e+08  \n",
       "709880  245500000.0  528739190.0  811978380.0  9.109892e+08  \n",
       "709881  245500000.0  528739190.0  811978380.0  9.109892e+08  \n",
       "709882  245500000.0  528739190.0  811978380.0  9.109892e+08  \n",
       "\n",
       "[709883 rows x 23 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio = pd.merge(cross,fenweishu,on='month')\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>38.34</td>\n",
       "      <td>2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.016010e+09</td>\n",
       "      <td>1.859497e+09</td>\n",
       "      <td>20.739149</td>\n",
       "      <td>21.343572</td>\n",
       "      <td>-0.122253</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.128345</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "      <td>Pmax</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>11.29</td>\n",
       "      <td>5</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.161200e+08</td>\n",
       "      <td>4.656752e+08</td>\n",
       "      <td>19.571632</td>\n",
       "      <td>19.958999</td>\n",
       "      <td>-0.030901</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.036993</td>\n",
       "      <td>-0.309901</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>9.90</td>\n",
       "      <td>5</td>\n",
       "      <td>16.0</td>\n",
       "      <td>4.950000e+07</td>\n",
       "      <td>1.237500e+08</td>\n",
       "      <td>17.717483</td>\n",
       "      <td>18.633774</td>\n",
       "      <td>-0.114490</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.120582</td>\n",
       "      <td>-0.460637</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "      <td>P5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000005</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>10.55</td>\n",
       "      <td>5</td>\n",
       "      <td>20.0</td>\n",
       "      <td>4.502265e+08</td>\n",
       "      <td>9.495000e+08</td>\n",
       "      <td>19.925261</td>\n",
       "      <td>20.671446</td>\n",
       "      <td>-0.123026</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.129118</td>\n",
       "      <td>-0.115097</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "      <td>P8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>600601</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>39.30</td>\n",
       "      <td>6</td>\n",
       "      <td>22.0</td>\n",
       "      <td>3.576300e+07</td>\n",
       "      <td>3.930000e+07</td>\n",
       "      <td>17.392424</td>\n",
       "      <td>17.486735</td>\n",
       "      <td>-0.124722</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.130814</td>\n",
       "      <td>0.184748</td>\n",
       "      <td>1</td>\n",
       "      <td>13107600.0</td>\n",
       "      <td>20750000.0</td>\n",
       "      <td>39300000.0</td>\n",
       "      <td>74354390.0</td>\n",
       "      <td>123750000.0</td>\n",
       "      <td>465675240.0</td>\n",
       "      <td>820000000.0</td>\n",
       "      <td>949500000.0</td>\n",
       "      <td>1.179490e+09</td>\n",
       "      <td>P3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>600602</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>505.00</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.479550e+08</td>\n",
       "      <td>1.010000e+09</td>\n",
       "      <td>19.328758</td>\n",
       "      <td>20.733216</td>\n",
       "      <td>0.009798</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.002868</td>\n",
       "      <td>0.010496</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "      <td>Pmax</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>600651</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>401.30</td>\n",
       "      <td>2</td>\n",
       "      <td>20.0</td>\n",
       "      <td>1.324290e+07</td>\n",
       "      <td>1.324290e+07</td>\n",
       "      <td>16.398972</td>\n",
       "      <td>16.398972</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>-0.041567</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "      <td>P2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>600652</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>219.60</td>\n",
       "      <td>2</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.756800e+06</td>\n",
       "      <td>1.756800e+06</td>\n",
       "      <td>14.379005</td>\n",
       "      <td>14.379005</td>\n",
       "      <td>0.062409</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.055479</td>\n",
       "      <td>0.028134</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "      <td>P1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>600654</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>397.80</td>\n",
       "      <td>2</td>\n",
       "      <td>22.0</td>\n",
       "      <td>8.353800e+06</td>\n",
       "      <td>8.357778e+07</td>\n",
       "      <td>15.938227</td>\n",
       "      <td>18.241288</td>\n",
       "      <td>0.041089</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.034159</td>\n",
       "      <td>0.081305</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "      <td>P4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>600656</td>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>329.20</td>\n",
       "      <td>2</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.481762e+08</td>\n",
       "      <td>8.119784e+08</td>\n",
       "      <td>18.813913</td>\n",
       "      <td>20.514984</td>\n",
       "      <td>0.088624</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.081694</td>\n",
       "      <td>-0.002070</td>\n",
       "      <td>1</td>\n",
       "      <td>7499850.0</td>\n",
       "      <td>13242900.0</td>\n",
       "      <td>48410340.0</td>\n",
       "      <td>83577780.0</td>\n",
       "      <td>164538890.0</td>\n",
       "      <td>245500000.0</td>\n",
       "      <td>528739190.0</td>\n",
       "      <td>811978380.0</td>\n",
       "      <td>9.109892e+08</td>\n",
       "      <td>P8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>700739 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month   price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "0       000001 1991-05-31   38.34     2  24.0   1.016010e+09  1.859497e+09   \n",
       "1       000002 1991-05-31   11.29     5  17.0   3.161200e+08  4.656752e+08   \n",
       "2       000004 1991-05-31    9.90     5  16.0   4.950000e+07  1.237500e+08   \n",
       "3       000005 1991-05-31   10.55     5  20.0   4.502265e+08  9.495000e+08   \n",
       "4       600601 1991-05-31   39.30     6  22.0   3.576300e+07  3.930000e+07   \n",
       "...        ...        ...     ...   ...   ...            ...           ...   \n",
       "709878  600602 1991-01-31  505.00     2  22.0   2.479550e+08  1.010000e+09   \n",
       "709879  600651 1991-01-31  401.30     2  20.0   1.324290e+07  1.324290e+07   \n",
       "709880  600652 1991-01-31  219.60     2  11.0   1.756800e+06  1.756800e+06   \n",
       "709881  600654 1991-01-31  397.80     2  22.0   8.353800e+06  8.357778e+07   \n",
       "709882  600656 1991-01-31  329.20     2  14.0   1.481762e+08  8.119784e+08   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "0       20.739149  21.343572 -0.122253  0.006092 -0.128345 -0.119551  1   \n",
       "1       19.571632  19.958999 -0.030901  0.006092 -0.036993 -0.309901  1   \n",
       "2       17.717483  18.633774 -0.114490  0.006092 -0.120582 -0.460637  1   \n",
       "3       19.925261  20.671446 -0.123026  0.006092 -0.129118 -0.115097  1   \n",
       "4       17.392424  17.486735 -0.124722  0.006092 -0.130814  0.184748  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "709878  19.328758  20.733216  0.009798  0.006930  0.002868  0.010496  1   \n",
       "709879  16.398972  16.398972  0.000000  0.006930 -0.006930 -0.041567  1   \n",
       "709880  14.379005  14.379005  0.062409  0.006930  0.055479  0.028134  1   \n",
       "709881  15.938227  18.241288  0.041089  0.006930  0.034159  0.081305  1   \n",
       "709882  18.813913  20.514984  0.088624  0.006930  0.081694 -0.002070  1   \n",
       "\n",
       "               one         two       three        four         five  \\\n",
       "0       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "1       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "2       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "3       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "4       13107600.0  20750000.0  39300000.0  74354390.0  123750000.0   \n",
       "...            ...         ...         ...         ...          ...   \n",
       "709878   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709879   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709880   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709881   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "709882   7499850.0  13242900.0  48410340.0  83577780.0  164538890.0   \n",
       "\n",
       "                six        seven        eight          nine  sort  \n",
       "0       465675240.0  820000000.0  949500000.0  1.179490e+09  Pmax  \n",
       "1       465675240.0  820000000.0  949500000.0  1.179490e+09    P6  \n",
       "2       465675240.0  820000000.0  949500000.0  1.179490e+09    P5  \n",
       "3       465675240.0  820000000.0  949500000.0  1.179490e+09    P8  \n",
       "4       465675240.0  820000000.0  949500000.0  1.179490e+09    P3  \n",
       "...             ...          ...          ...           ...   ...  \n",
       "709878  245500000.0  528739190.0  811978380.0  9.109892e+08  Pmax  \n",
       "709879  245500000.0  528739190.0  811978380.0  9.109892e+08    P2  \n",
       "709880  245500000.0  528739190.0  811978380.0  9.109892e+08    P1  \n",
       "709881  245500000.0  528739190.0  811978380.0  9.109892e+08    P4  \n",
       "709882  245500000.0  528739190.0  811978380.0  9.109892e+08    P8  \n",
       "\n",
       "[700739 rows x 24 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio['sort'] = np.where(\n",
    "    portfolio['totalvalue'] <= portfolio['one'], 'P1',\n",
    "    np.where(\n",
    "        portfolio['totalvalue'] <= portfolio['two'], 'P2',\n",
    "        np.where(\n",
    "            portfolio['totalvalue'] <= portfolio['three'], 'P3',\n",
    "            np.where(\n",
    "                portfolio['totalvalue'] <= portfolio['four'], 'P4',\n",
    "                np.where(\n",
    "                    portfolio['totalvalue'] <= portfolio['five'], 'P5',\n",
    "                    np.where(\n",
    "                        portfolio['totalvalue'] <= portfolio['six'], 'P6',\n",
    "                        np.where(\n",
    "                            portfolio['totalvalue'] <= portfolio['seven'], 'P7',\n",
    "                            np.where(\n",
    "                                portfolio['totalvalue'] <= portfolio['eight'], 'P8',\n",
    "                                np.where(\n",
    "                                    portfolio['totalvalue'] <= portfolio['nine'],\n",
    "                                    'P9', 'Pmax')))))))))\n",
    "portfolio = portfolio.dropna(subset=['floatingvalue','next_ret'])\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "<lambda>() got an unexpected keyword argument 'include_groups'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1422\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1423\u001b[1;33m                 \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_python_apply_general\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_selected_obj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1424\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m_python_apply_general\u001b[1;34m(self, f, data, not_indexed_same)\u001b[0m\n\u001b[0;32m   1463\u001b[0m         \"\"\"\n\u001b[1;32m-> 1464\u001b[1;33m         \u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmutated\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgrouper\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1465\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\ops.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, data, axis)\u001b[0m\n\u001b[0;32m    760\u001b[0m             \u001b[0mgroup_axes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgroup\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 761\u001b[1;33m             \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgroup\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    762\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mmutated\u001b[0m \u001b[1;32mand\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0m_is_indexed_like\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgroup_axes\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mf\u001b[1;34m(g)\u001b[0m\n\u001b[0;32m   1396\u001b[0m                     \u001b[1;32mwith\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0merrstate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"ignore\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1397\u001b[1;33m                         \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1398\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: <lambda>() got an unexpected keyword argument 'include_groups'",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_25848\\2701615134.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m portfolio_size =  pd.DataFrame(\n\u001b[1;32m----> 2\u001b[1;33m     portfolio.groupby(['month','sort']).apply(lambda x: np.average(x['next_ret'],weights = x['totalvalue']),include_groups=False))\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[0mportfolio_size\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, func, *args, **kwargs)\u001b[0m\n\u001b[0;32m   1432\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1433\u001b[0m                 \u001b[1;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_group_selection_context\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1434\u001b[1;33m                     \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_python_apply_general\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_selected_obj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1435\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1436\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36m_python_apply_general\u001b[1;34m(self, f, data, not_indexed_same)\u001b[0m\n\u001b[0;32m   1462\u001b[0m             \u001b[0mdata\u001b[0m \u001b[0mafter\u001b[0m \u001b[0mapplying\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1463\u001b[0m         \"\"\"\n\u001b[1;32m-> 1464\u001b[1;33m         \u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmutated\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgrouper\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdata\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1465\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1466\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mnot_indexed_same\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\ops.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, data, axis)\u001b[0m\n\u001b[0;32m    759\u001b[0m             \u001b[1;31m# group might be modified\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    760\u001b[0m             \u001b[0mgroup_axes\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgroup\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0maxes\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 761\u001b[1;33m             \u001b[0mres\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mgroup\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    762\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mmutated\u001b[0m \u001b[1;32mand\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0m_is_indexed_like\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mres\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mgroup_axes\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    763\u001b[0m                 \u001b[0mmutated\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mc:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\groupby\\groupby.py\u001b[0m in \u001b[0;36mf\u001b[1;34m(g)\u001b[0m\n\u001b[0;32m   1395\u001b[0m                 \u001b[1;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mg\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1396\u001b[0m                     \u001b[1;32mwith\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0merrstate\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mall\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"ignore\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1397\u001b[1;33m                         \u001b[1;32mreturn\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mg\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m*\u001b[0m\u001b[0margs\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1398\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1399\u001b[0m             \u001b[1;32melif\u001b[0m \u001b[0mhasattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnanops\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"nan\"\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mfunc\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: <lambda>() got an unexpected keyword argument 'include_groups'"
     ]
    }
   ],
   "source": [
    "portfolio_size =  pd.DataFrame(\n",
    "    portfolio.groupby(['month','sort']).apply(lambda x: np.average(x['next_ret'],weights = x['totalvalue']),include_groups=False))\n",
    "portfolio_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_size = portfolio_size.reset_index()\n",
    "portfolio_size.columns = ['month', 'sort', 'p']\n",
    "portfolio_size['month'] = portfolio_size['month'] + MonthEnd(1)\n",
    "portfolio_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_size = portfolio_size.pivot_table(index='month',\n",
    "                                            columns='sort',\n",
    "                                            values='p')\n",
    "portfolio_size['My_portfolio'] = portfolio_size['P1'] - portfolio_size['Pmax']\n",
    "portfolio_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_size.index = pd.to_datetime(portfolio_size.index)\n",
    "portfolio_size = portfolio_size['1995-01':'2023-12'].copy()\n",
    "portfolio_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_port = smf.ols('My_portfolio ~ 1',\n",
    "                 data=portfolio_size['2000-01':'2022-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model_port.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('C:/Users/21230/Desktop/Python-homework/000001.csv')\n",
    "data['Day'] = pd.to_datetime(data['Day'],format='%Y/%m/%d')\n",
    "data.set_index('Day', inplace = True)\n",
    "data.sort_values(by = ['Day'],axis=0, ascending=True)\n",
    "data_new = data['1995-01':'2024-09'].copy()\n",
    "data_new['Close'] = pd.to_numeric(data_new['Close'])\n",
    "data_new['Preclose'] = pd.to_numeric(data_new['Preclose'])\n",
    "data_new['Raw_return'] = data_new['Close'] / data_new['Preclose'] - 1\n",
    "Month_data = data_new.resample('ME')['Raw_return'].apply(lambda x: (1+x).prod() - 1).to_frame()\n",
    "Month_data.reset_index(inplace = True)\n",
    "Month_data.rename(columns = {'Day':'month'}, inplace = True)\n",
    "Month_data.set_index('month', inplace = True)\n",
    "Month_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MYPOR = portfolio_size[['P1','Pmax','My_portfolio']]\n",
    "MYPOR = MYPOR.dropna()\n",
    "MYPOR = MYPOR['1995-01':'2023-12']\n",
    "MYPOR = pd.merge(MYPOR,Month_data[['Raw_return']],on='month',how='left')\n",
    "MYPOR['month'] = pd.date_range(start='1995-01', periods=len(MYPOR), freq='ME')\n",
    "MYPOR.set_index('month',inplace=True)\n",
    "MYPOR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_port = smf.ols('My_portfolio ~ Raw_return',\n",
    "                 data=MYPOR['2000-01':'2023-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model_port.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cross"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from linearmodels import FamaMacBeth\n",
    "# subset the cross data from 2000-01 to 2023-12\n",
    "cross_reg = cross[cross['month'] >= '2000-01']\n",
    "cross_reg = cross_reg.set_index(['Stkcd', 'month']) # 设置multi-index\n",
    "\n",
    "model = FamaMacBeth.from_formula('next_ret ~ 1 + sizet', data=cross_reg.dropna(subset=['next_ret','sizet']))\n",
    "# 一般fm回归结果展示的是Newey-West调整后的t值，.fit()中做如下设置\n",
    "# 其中`bandwidth`是Newey-West滞后阶数，选取方式为lag = 4(T/100) ^ (2/9)\n",
    "# 若不需要Newey-West调整则去掉括号内所有设置。\n",
    "# choose bandwidth auto\n",
    "res = model.fit(cov_type= 'kernel',debiased = False,bandwidth=6)\n",
    "print(res.summary)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 规模策略的Sharpe Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算投资组合的Sharpe Ratio\n",
    "sharpe_ratio = MYPOR['My_portfolio'].mean() / MYPOR['My_portfolio'].std() * np.sqrt(12)\n",
    "print(f\"Sharpe Ratio: {sharpe_ratio}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 还原投资策略的价格\n",
    "MYPOR['price_portfolio'] = (1 + MYPOR['My_portfolio']).cumprod()\n",
    "MYPOR['price_p1'] = (1 + MYPOR['P1']).cumprod()\n",
    "MYPOR['price_pmax'] = (1 + MYPOR['Pmax']).cumprod()\n",
    "MYPOR['price_market'] = (1 + MYPOR['Raw_return']).cumprod()\n",
    "MYPOR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 规模策略价格图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(20, 6))\n",
    "plt.plot(\n",
    "    'price_portfolio',  \n",
    "    '.-r', \n",
    "    label='Price of My Portfolio',  \n",
    "    linewidth=1, \n",
    "    data=MYPOR)  \n",
    "plt.title(\"China's Stock Market\") \n",
    "plt.xlabel('Month') \n",
    "plt.ylabel('Return') \n",
    "\n",
    "plt.plot(\n",
    "    'price_market', \n",
    "    '.-b', \n",
    "    label='Price of Market', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "\n",
    "plt.plot(\n",
    "    'price_p1', \n",
    "    '.-g', \n",
    "    label='Price of Lowest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "\n",
    "plt.plot(\n",
    "    'price_pmax', \n",
    "    '.-c', \n",
    "    label='Price of Highest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "plt.legend() # 显示图例\n",
    "fig = plt.gcf()\n",
    "plt.show();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 规模策略的最大回测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算累积收益率\n",
    "MYPOR['cumulative_return'] = (1 + MYPOR['My_portfolio']).cumprod()\n",
    "\n",
    "# 计算滚动最大值\n",
    "MYPOR['rolling_max'] = MYPOR['cumulative_return'].cummax()\n",
    "\n",
    "# 计算回撤\n",
    "MYPOR['drawdown'] = MYPOR['cumulative_return'] / MYPOR['rolling_max'] - 1\n",
    "\n",
    "# 计算最大回撤\n",
    "max_drawdown = MYPOR['drawdown'].min()\n",
    "print(f\"Maximum Drawdown: {max_drawdown}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 找出最大回撤的时间\n",
    "max_drawdown_end = MYPOR['drawdown'].idxmin() # 返回 Series 中最小值的索引\n",
    "max_drawdown_start = MYPOR.loc[:max_drawdown_end, 'cumulative_return'].idxmax() # 这部分代码选择了从数据开始到 max_drawdown_end 时间点之间的所有累积收益率\n",
    "\n",
    "print(f\"Maximum Drawdown: {max_drawdown}\")\n",
    "print(f\"Maximum Drawdown Start Date: {max_drawdown_start}\")\n",
    "print(f\"Maximum Drawdown End Date: {max_drawdown_end}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Different Periods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MYPOR_sub = MYPOR['2000-01':'2008-12'].copy()\n",
    "# 还原投资策略的价格\n",
    "MYPOR_sub['price_portfolio'] = (1 + MYPOR_sub['My_portfolio']).cumprod()\n",
    "MYPOR_sub['price_p1'] = (1 + MYPOR_sub['P1']).cumprod()\n",
    "MYPOR_sub['price_pmax'] = (1 + MYPOR_sub['Pmax']).cumprod()\n",
    "MYPOR_sub['price_market'] = (1 + MYPOR_sub['Raw_return']).cumprod()\n",
    "MYPOR_sub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(20, 6))\n",
    "plt.plot(\n",
    "    'price_portfolio',  \n",
    "    '.-b', \n",
    "    label='Price of My Portfolio',  \n",
    "    linewidth=1, \n",
    "    data=MYPOR_sub)  \n",
    "plt.title(\"China's Stock Market\") \n",
    "plt.xlabel('Month') \n",
    "plt.ylabel('Return') \n",
    "\n",
    "plt.plot(\n",
    "    'price_market', \n",
    "    '.-r', \n",
    "    label='Price of Market', \n",
    "    linewidth=1, \n",
    "    data=MYPOR_sub) \n",
    "\n",
    "plt.plot(\n",
    "    'price_p1', \n",
    "    '.-g', \n",
    "    label='Price of Lowest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR_sub) \n",
    "\n",
    "plt.plot(\n",
    "    'price_pmax', \n",
    "    '.-c', \n",
    "    label='Price of Highest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR_sub) \n",
    "plt.legend() # 显示图例\n",
    "fig = plt.gcf()\n",
    "plt.show();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1月效应"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_size_Jan = portfolio_size.copy()\n",
    "portfolio_size_Jan = portfolio_size_Jan.reset_index()\n",
    "portfolio_size_Jan['yue'] = portfolio_size_Jan['month'].dt.month\n",
    "portfolio_size_Jan = portfolio_size_Jan[portfolio_size_Jan['yue'] != 1]\n",
    "portfolio_size_Jan.set_index(['month'],inplace=True)\n",
    "portfolio_size_Jan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_port = smf.ols('My_portfolio ~ 1',\n",
    "                 data=portfolio_size_Jan['1995-01':'2023-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model_port.summary())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 价值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "EP = pd.read_csv('C:/Users/21230/Desktop/Python-homework/python-homework-ykl-1/EP2023.csv')\n",
    "EP['month'] = pd.to_datetime(EP['month'], format='%Y-%m-%d') + MonthEnd(1)\n",
    "EP = EP[['Stkcd','month','ep','ep_recent']]\n",
    "EP['Stkcd'] = EP['Stkcd'].apply(lambda x: '{:0>6}'.format(x)) # 6位股票代码\n",
    "EP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cross = pd.merge(cross,EP[['Stkcd','month','ep','ep_recent']],on=['Stkcd','month'],how='left')\n",
    "cross"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fenweishu = pd.DataFrame(\n",
    "    cross.groupby(['month'])['ep'].quantile([0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]))\n",
    "fenweishu = fenweishu.reset_index()\n",
    "fenweishu = fenweishu.pivot_table(index='month',columns='level_1',values='ep')\n",
    "fenweishu.columns = ['one','two','three','four','five','six','seven','eight','nine']\n",
    "fenweishu\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio = pd.merge(cross,fenweishu,on='month')\n",
    "portfolio['sort'] = np.where(\n",
    "    portfolio['ep'] <= portfolio['one'], 'P1',\n",
    "    np.where(\n",
    "        portfolio['ep'] <= portfolio['two'], 'P2',\n",
    "        np.where(\n",
    "            portfolio['ep'] <= portfolio['three'], 'P3',\n",
    "            np.where(\n",
    "                portfolio['ep'] <= portfolio['four'], 'P4',\n",
    "                np.where(\n",
    "                    portfolio['ep'] <= portfolio['five'], 'P5',\n",
    "                    np.where(\n",
    "                        portfolio['ep'] <= portfolio['six'], 'P6',\n",
    "                        np.where(\n",
    "                            portfolio['ep'] <= portfolio['seven'], 'P7',\n",
    "                            np.where(\n",
    "                                portfolio['ep'] <= portfolio['eight'], 'P8',\n",
    "                                np.where(\n",
    "                                    portfolio['ep'] <= portfolio['nine'],\n",
    "                                    'P9', 'Pmax')))))))))\n",
    "portfolio = portfolio.dropna(subset=['floatingvalue','next_ret','ep'])\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value =  pd.DataFrame(\n",
    "    portfolio.groupby(['month','sort']).apply(lambda x: np.average(x['next_ret'],weights = x['floatingvalue']),include_groups=False))\n",
    "portfolio_value = portfolio_value.reset_index()\n",
    "portfolio_value.columns = ['month', 'sort', 'p']\n",
    "portfolio_value['month'] = portfolio_value['month'] + MonthEnd(1)\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value = portfolio_value.pivot_table(index='month',\n",
    "                                                    columns='sort',\n",
    "                                                    values='p')\n",
    "portfolio_value['My_portfolio'] = portfolio_value['Pmax'] - portfolio_value['P1']\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value = portfolio_value['1995-01':'2023-12'].copy()\n",
    "portfolio_value['month'] = pd.date_range(start='1995-01', periods=len(portfolio_value), freq='M')\n",
    "portfolio_value.set_index('month', inplace = True)\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_port = smf.ols('My_portfolio ~ 1',\n",
    "                 data=portfolio_value['2000-01':'2023-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model_port.summary())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 如何改进"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fenweishu = pd.DataFrame(\n",
    "    cross.groupby(['month'])['totalvalue'].quantile(0.3))\n",
    "fenweishu.columns = ['fenweishu_guimo']\n",
    "fenweishu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cross_new = pd.merge(cross,fenweishu,on='month',how='left')\n",
    "cross_new = cross_new[cross_new['totalvalue'] > cross_new['fenweishu_guimo']]\n",
    "cross_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fenweishu = pd.DataFrame(\n",
    "    cross_new.groupby(['month'])['ep'].quantile([0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]))\n",
    "fenweishu = fenweishu.reset_index()\n",
    "fenweishu = fenweishu.pivot_table(index='month',columns='level_1',values='ep')\n",
    "fenweishu.columns = ['one','two','three','four','five','six','seven','eight','nine']\n",
    "fenweishu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio = pd.merge(cross_new,fenweishu,on='month')\n",
    "portfolio['sort'] = np.where(\n",
    "    portfolio['ep'] <= portfolio['one'], 'P1',\n",
    "    np.where(\n",
    "        portfolio['ep'] <= portfolio['two'], 'P2',\n",
    "        np.where(\n",
    "            portfolio['ep'] <= portfolio['three'], 'P3',\n",
    "            np.where(\n",
    "                portfolio['ep'] <= portfolio['four'], 'P4',\n",
    "                np.where(\n",
    "                    portfolio['ep'] <= portfolio['five'], 'P5',\n",
    "                    np.where(\n",
    "                        portfolio['ep'] <= portfolio['six'], 'P6',\n",
    "                        np.where(\n",
    "                            portfolio['ep'] <= portfolio['seven'], 'P7',\n",
    "                            np.where(\n",
    "                                portfolio['ep'] <= portfolio['eight'], 'P8',\n",
    "                                np.where(\n",
    "                                    portfolio['ep'] <= portfolio['nine'],\n",
    "                                    'P9', 'Pmax')))))))))\n",
    "portfolio = portfolio.dropna(subset=['floatingvalue','next_ret','ep'])\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value =  pd.DataFrame(\n",
    "    portfolio.groupby(['month','sort']).apply(lambda x: np.average(x['next_ret'],weights = x['floatingvalue']),include_groups=False))\n",
    "portfolio_value = portfolio_value.reset_index()\n",
    "portfolio_value.columns = ['month', 'sort', 'p']\n",
    "portfolio_value['month'] = portfolio_value['month'] + MonthEnd(1)\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value = portfolio_value.pivot_table(index='month',\n",
    "                                                    columns='sort',\n",
    "                                                    values='p')\n",
    "portfolio_value['My_portfolio'] = portfolio_value['Pmax'] - portfolio_value['P1']\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "portfolio_value = portfolio_value['1995-01':'2023-12'].copy()\n",
    "portfolio_value['month'] = pd.date_range(start='1995-01', periods=len(portfolio_value), freq='M')\n",
    "portfolio_value.set_index('month', inplace = True)\n",
    "portfolio_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_port = smf.ols('My_portfolio ~ 1',\n",
    "                 data=portfolio_value['1995-01':'2023-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model_port.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from linearmodels import FamaMacBeth\n",
    "# subset the cross data from 2000-01 to 2023-12\n",
    "cross_reg = cross_new[cross_new['month'] >= '2000-01']\n",
    "cross_reg = cross_reg.set_index(['Stkcd', 'month']) # 设置multi-index\n",
    "\n",
    "model = FamaMacBeth.from_formula('next_ret ~ 1 + sizet + ep', data=cross_reg.dropna(subset=['next_ret','sizet','ep']))\n",
    "# 一般fm回归结果展示的是Newey-West调整后的t值，.fit()中做如下设置\n",
    "# 其中`bandwidth`是Newey-West滞后阶数，选取方式为lag = 4(T/100) ^ (2/9)\n",
    "# 若不需要Newey-West调整则去掉括号内所有设置。\n",
    "# choose bandwidth auto\n",
    "res = model.fit(cov_type= 'kernel',debiased = False,bandwidth=6)\n",
    "print(res.summary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "MYPOR = portfolio_value[['P1','Pmax','My_portfolio']]\n",
    "MYPOR = MYPOR.dropna()\n",
    "MYPOR = MYPOR['1995-01':'2023-12']\n",
    "MYPOR = pd.merge(MYPOR,Month_data[['Raw_return']],on='month',how='left')\n",
    "MYPOR['month'] = pd.date_range(start='1995-01', periods=len(MYPOR), freq='M')\n",
    "MYPOR.set_index('month',inplace=True)\n",
    "MYPOR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 还原投资策略的价格\n",
    "MYPOR['price_portfolio'] = (1 + MYPOR['My_portfolio']).cumprod()\n",
    "MYPOR['price_p1'] = (1 + MYPOR['P1']).cumprod()\n",
    "MYPOR['price_pmax'] = (1 + MYPOR['Pmax']).cumprod()\n",
    "MYPOR['price_market'] = (1 + MYPOR['Raw_return']).cumprod()\n",
    "MYPOR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.figure(figsize=(19, 7))\n",
    "plt.plot(\n",
    "    'price_portfolio',  \n",
    "    '.-r', \n",
    "    label='Price of My Portfolio',  \n",
    "    linewidth=1, \n",
    "    data=MYPOR)  \n",
    "plt.title(\"China's Stock Market\") \n",
    "plt.xlabel('Month') \n",
    "plt.ylabel('Return') \n",
    "\n",
    "plt.plot(\n",
    "    'price_market', \n",
    "    '.-b', \n",
    "    label='Price of Market', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "\n",
    "plt.plot(\n",
    "    'price_p1', \n",
    "    '.-g', \n",
    "    label='Price of Lowest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "\n",
    "plt.plot(\n",
    "    'price_pmax', \n",
    "    '.-c', \n",
    "    label='Price of Highest', \n",
    "    linewidth=1, \n",
    "    data=MYPOR) \n",
    "plt.legend() # 显示图例\n",
    "fig = plt.gcf()\n",
    "plt.show();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 价值策略Sharpe Ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算投资组合的Sharpe Ratio\n",
    "sharpe_ratio = MYPOR['My_portfolio'].mean() / MYPOR['My_portfolio'].std() * np.sqrt(11)\n",
    "print(f\"Sharpe Ratio: {sharpe_ratio}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 价值策略最大回测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 计算累积收益率\n",
    "MYPOR['cumulative_return'] = (1 + MYPOR['My_portfolio']).cumprod()\n",
    "\n",
    "# 计算滚动最大值\n",
    "MYPOR['rolling_max'] = MYPOR['cumulative_return'].cummax()\n",
    "\n",
    "# 计算回撤\n",
    "MYPOR['drawdown'] = MYPOR['cumulative_return'] / MYPOR['rolling_max'] - 1\n",
    "\n",
    "# 计算最大回撤\n",
    "max_drawdown = MYPOR['drawdown'].min()\n",
    "print(f\"Maximum Drawdown: {max_drawdown}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 找出最大回撤的时间\n",
    "max_drawdown_end = MYPOR['drawdown'].idxmin() # 返回 Series 中最小值的索引\n",
    "max_drawdown_start = MYPOR.loc[:max_drawdown_end, 'cumulative_return'].idxmax() # 这部分代码选择了从数据开始到 max_drawdown_end 时间点之间的所有累积收益率\n",
    "\n",
    "print(f\"Maximum Drawdown: {max_drawdown}\")\n",
    "print(f\"Maximum Drawdown Start Date: {max_drawdown_start}\")\n",
    "print(f\"Maximum Drawdown End Date: {max_drawdown_end}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
